# -*- coding:utf-8 -*-
import os
from collections import OrderedDict
import sys
import xlrd
import json
from colored import color

inputPath = sys.argv[1]
print('操作表格：', inputPath)

exportPath = "./export/"
""" 打开excel表格"""
workbook = xlrd.open_workbook(inputPath)
print(workbook)             # 结果：<xlrd.book.Book object at 0x000000000291B128>

""" 获取所有sheet名称"""


def findSheets():
    sheet_names = workbook.sheet_names()
    length = len(sheet_names)
    print(sheet_names, '   len=', length)          # 结果：['表1', 'Sheet2']

    dic = OrderedDict()  # 有序字典
    for sheet_name in sheet_names:
        dic[sheet_name] = readSheet(sheet_name)

    (filepath, tempfilename) = os.path.split(inputPath)
    (filename, extension) = os.path.splitext(tempfilename)
    saveToJsonFile(dic, filename+'.json')


""" 类型转换"""


def convertType(value, vtype):
    if vtype == 'int':
        value = value and int(value) or 0
    elif vtype == 'float':
        value = value and float(value) or 0
    elif vtype == 'string':
        value = value and str(value) or ""
    elif vtype == 'array':
        value = value and list(eval(value)) or []
    elif vtype == 'json':
        value = value and json.loads(value) or {}#json.dumps(value, ensure_ascii=False)
        pass

    return value


""" 通过sheet名称获取表 转换成字典"""


def readSheet(name):  # 结果 {key:{id:value,name:value,...},key:{id:value},...}
    "readSheet"
    sheet_object = workbook.sheet_by_name(sheet_name=name)
    nrows = sheet_object.nrows
    ncols = sheet_object.ncols  # 列数
    titleCh = sheet_object.row_values(0)  # 表头中文注解
    titleEn = sheet_object.row_values(1)  # 表头英文字段
    titleCS = sheet_object.row_values(2)  # 表头客户端、服务端
    jtype = sheet_object.row_values(3)  # 表头，类型

    # if title[0] == 'id':

    dic = OrderedDict()  # 有序字典
    for row in range(4, nrows):
        row_values = sheet_object.row_values(rowx=row)
        single = OrderedDict()
        for col in range(0, ncols):
            if len(titleEn[col]) == 0:
                continue
            cell_value = row_values[col]
            if cell_value:
                try:
                    convert_value = convertType(cell_value, jtype[col])
                    single[titleEn[col]] = convertType(cell_value, jtype[col])
                except Exception as e:
                    print('\033[31mError::sheet='+name+'>>> col:'+str(col+1)+' row:'+str(row+1)+'\033[0m')

            else:
                try:
                    convert_value = convertType(cell_value, jtype[col])
                    single[titleEn[col]] = convertType(cell_value, jtype[col])
                except Exception as e:
                    print('\033[31mError::sheet='+name+'>>> col:'+str(col+1)+' row:'+str(row+1)+'\033[0m')
                pass
        
        # print('\033[31m这是前景色1')
        # print (color.red('I am red!'))
        try:
            dic[convertType(row_values[0], jtype[0])] = single
        except Exception as e:
            color.red('Error::sheet='+name+'  第一列类型有问题')
        

    return dic


def saveToJsonFile(data, filename):
    json_obj = json.dumps(data, ensure_ascii=False)
    # print(filename,json_obj)
    with open(exportPath+filename, "w", encoding="utf8") as f:
        f.write(json_obj)


findSheets()
